﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Diagnostics;
using System.ServiceModel;
using System.Runtime.Serialization;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;


namespace DragDraw.Service
{
    public class WidgetInfoData : SqlDataManager
    {
        ///<summary>
        ///插入WidgetInfo信息
        ///</summary>
        ///<exception cref="DataErrorException">数据库执行错误</exception>
        ///<exception cref="DataNotFoundException">查找不到相应记录</exception>
        static public int Insert(WidgetInfo widgetInfo)//what if the id exist?
        {
            SqlCommand curCommand;
            curCommand = new SqlCommand();
            curCommand.CommandType = CommandType.StoredProcedure;
            curCommand.CommandText = "InsertWidgetInfo";
            curCommand.Parameters.Add("@result", SqlDbType.Int);
            curCommand.Parameters.Add("@widgetID",SqlDbType.Int);
            curCommand.Parameters.Add("@ownerSpaceID", SqlDbType.Int);
            curCommand.Parameters.Add("@widgetLibInfoID", SqlDbType.Int);
            curCommand.Parameters.Add("@posX", SqlDbType.Int);
            curCommand.Parameters.Add("@posY", SqlDbType.Int);
            curCommand.Parameters.Add("@posZ", SqlDbType.Int);
            curCommand.Parameters.Add("@height", SqlDbType.Int);
            curCommand.Parameters.Add("@width", SqlDbType.Int);
            curCommand.Parameters["@result"].Direction = ParameterDirection.Output;
            curCommand.Parameters["@widgetID"].Direction = ParameterDirection.Output;
            curCommand.Parameters["@ownerSpaceID"].Value = widgetInfo.OwnerSpaceID;
            curCommand.Parameters["@widgetLibInfoID"].Value = widgetInfo.WidgetLibInfoID;
            curCommand.Parameters["@posX"].Value = widgetInfo.PosX;
            curCommand.Parameters["@posY"].Value = widgetInfo.PosY;
            curCommand.Parameters["@posZ"].Value = widgetInfo.PosZ;
            curCommand.Parameters["@height"].Value = widgetInfo.Height;
            curCommand.Parameters["@width"].Value = widgetInfo.Width;

            curCommand.Connection = SqlDataManager.OpenConnection();
            int result = 0;
            try
            {
                result = curCommand.ExecuteNonQuery();
            }
            catch (Exception error)
            {
                Console.WriteLine(error.Message);
                throw new DataErrorException("WidgetInfoData.Insert:执行SqlCommand的时候发生错误！", error);
            }
            finally
            {
                SqlDataManager.CloseConnection(curCommand.Connection);
            }
            if (Convert.ToInt32(curCommand.Parameters["@result"].Value) == 0)//有没有执行sql语句？
            {
                throw new DataInsertException("WidgetInfoData.Insert:没有执行sql语句！", null);
            }
            if (result <= 0)//执行的sql语句是否成功？
            {
                throw new DataErrorException("WidgetInfoData.Insert:执行了sql语句，可是sql语句没有影响任何一条记录！", null);
            }
            return Convert.ToInt32(curCommand.Parameters["@widgetID"].Value);
        }
        ///<summary>
        ///删除WidgetInfo信息
        ///</summary>
        ///<exception cref="DataErrorException">数据库执行错误</exception>
        ///<exception cref="DataNotFoundException">查找不到相应记录</exception>
        static public bool Delete(int widgetID)
        {
            SqlCommand curCommand;
            curCommand = new SqlCommand();
            curCommand.CommandType = CommandType.StoredProcedure;
            curCommand.CommandText = "DeleteWidgetInfo";
            curCommand.Parameters.Add("@result", SqlDbType.Int);
            curCommand.Parameters.Add("@widgetID", SqlDbType.Int);
            curCommand.Parameters["@result"].Direction = ParameterDirection.Output;
            curCommand.Parameters["@widgetID"].Value = widgetID;

            curCommand.Connection = SqlDataManager.OpenConnection();
            int result = 0;
            try
            {
                result = curCommand.ExecuteNonQuery();
            }
            catch (Exception error)
            {
                Console.WriteLine(error.Message);
                throw new DataErrorException("WidgetInfoData.Delete:执行SqlCommand的时候发生错误！", error);
            }
            finally
            {
                SqlDataManager.CloseConnection(curCommand.Connection);
            }
            if (Convert.ToInt32(curCommand.Parameters["@result"].Value) == 0)//有没有执行sql语句？
            {
                throw new DataErrorException("WidgetInfoData.Delete:没有执行sql语句！", null);
            }
            if (result <= 0)//执行的sql语句是否成功？
            {
                throw new DataErrorException("WidgetInfoData.Delete:执行了sql语句，可是sql语句没有影响任何一条记录！", null);
            }
            return true;
        }
        ///<summary>
        ///更新WidgetInfo信息
        ///</summary>
        ///<exception cref="DataErrorException">数据库执行错误</exception>
        ///<exception cref="DataNotFoundException">查找不到相应记录</exception>
        static public bool Update(WidgetInfo widgetInfo)
        {
            SqlCommand curCommand;
            curCommand = new SqlCommand();
            curCommand.CommandType = CommandType.StoredProcedure;
            curCommand.CommandText = "UpdateWidgetInfo";
            curCommand.Parameters.Add("@result", SqlDbType.Int);
            curCommand.Parameters.Add("@widgetID", SqlDbType.Int);
            curCommand.Parameters.Add("@ownerSpaceID", SqlDbType.Int);
            curCommand.Parameters.Add("@widgetLibInfoID", SqlDbType.Int);
            curCommand.Parameters.Add("@posX", SqlDbType.Int);
            curCommand.Parameters.Add("@posY", SqlDbType.Int);
            curCommand.Parameters.Add("@posZ", SqlDbType.Int);
            curCommand.Parameters.Add("@height", SqlDbType.Int);
            curCommand.Parameters.Add("@width", SqlDbType.Int);
            curCommand.Parameters["@result"].Direction = ParameterDirection.Output;
            curCommand.Parameters["@widgetID"].Value = widgetInfo.WidgetID;
            curCommand.Parameters["@ownerSpaceID"].Value = widgetInfo.OwnerSpaceID;
            curCommand.Parameters["@widgetLibInfoID"].Value = widgetInfo.WidgetLibInfoID;
            curCommand.Parameters["@posX"].Value = widgetInfo.PosX;
            curCommand.Parameters["@posY"].Value = widgetInfo.PosY;
            curCommand.Parameters["@posZ"].Value = widgetInfo.PosZ;
            curCommand.Parameters["@height"].Value = widgetInfo.Height;
            curCommand.Parameters["@width"].Value = widgetInfo.Width;

            curCommand.Connection = SqlDataManager.OpenConnection();
            int result = 0;
            try
            {
                result = curCommand.ExecuteNonQuery();
            }
            catch (Exception error)
            {
                Console.WriteLine(error.Message);
                throw new DataErrorException("WidgetInfoData.Update:执行SqlCommand的时候发生错误！", error);
            }
            finally
            {
                SqlDataManager.CloseConnection(curCommand.Connection);
            }
            if (Convert.ToInt32(curCommand.Parameters["@result"].Value) == 0)//有没有执行sql语句？
            {
                throw new DataErrorException("WidgetInfoData.Update:没有执行sql语句！", null);
            }
            if (result <= 0)//执行的sql语句是否成功？
            {
                throw new DataErrorException("WidgetInfoData.Update:执行了sql语句，可是sql语句没有影响任何一条记录！", null);
            }
            return true;
        }
        ///<summary>
        ///根据OwnerSpaceID查询WidgetInfo信息
        ///</summary>
        ///<exception cref="DataErrorException">数据库执行错误</exception>
        ///<exception cref="DataNotFoundException">查找不到相应记录</exception>
        static public List<WidgetInfo> QueryList(int ownerSpaceID)//if does not exist,return null
        {
            List<WidgetInfo> result_widgetInfoList = new List<WidgetInfo>();
            SqlCommand curCommand;
            curCommand = new SqlCommand();
            curCommand.CommandType = CommandType.Text;
            curCommand.CommandText = "select * from WidgetInfo where OwnerSpaceID='" + ownerSpaceID.ToString() + "'";

            curCommand.Connection = SqlDataManager.OpenConnection();
            try
            {
                SqlDataReader reader = curCommand.ExecuteReader(CommandBehavior.Default);
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        WidgetInfo temp_widgetInfo = new WidgetInfo();
                        temp_widgetInfo.WidgetID = reader.GetInt32(0);
                        temp_widgetInfo.OwnerSpaceID = reader.GetInt32(1);
                        temp_widgetInfo.WidgetLibInfoID = reader.GetInt32(2);
                        temp_widgetInfo.PosX = reader.GetInt32(3);
                        temp_widgetInfo.PosY = reader.GetInt32(4);
                        temp_widgetInfo.PosZ = reader.GetInt32(5);
                        temp_widgetInfo.Height = reader.GetInt32(6);
                        temp_widgetInfo.Width = reader.GetInt32(7);
                        result_widgetInfoList.Add(temp_widgetInfo);
                    }
                }
                else
                {
                    throw new DataNotFoundException("WidgetInfoData.QueryList:顺利执行完sql语句，可是没有找到匹配的记录！", null);
                }
            }
            catch (Exception error)
            {
                Console.WriteLine(error.Message);
                throw new DataErrorException("WidgetInfoData.QueryList:执行SqlCommand的时候发生错误！", error);
            }
            finally
            {
                SqlDataManager.CloseConnection(curCommand.Connection);
            }
            return result_widgetInfoList;
        }
        ///<summary>
        ///根据WidgetID查询WidgetInfo信息
        ///</summary>
        ///<exception cref="DataErrorException">数据库执行错误</exception>
        ///<exception cref="DataNotFoundException">查找不到相应记录</exception>
        static public WidgetInfo Query(int widgetID)//if does not exist,return null
        {
            WidgetInfo result_widgetInfo = new WidgetInfo();
            SqlCommand curCommand;
            curCommand = new SqlCommand();
            curCommand.CommandType = CommandType.Text;
            curCommand.CommandText = "select * from WidgetInfo where WidgetID='" + widgetID.ToString() + "'";

            curCommand.Connection = SqlDataManager.OpenConnection();
            bool HasResult = false;
            try
            {
                SqlDataReader reader = curCommand.ExecuteReader(CommandBehavior.SingleRow);
                if (reader.HasRows)
                {
                    HasResult = true;
                    while (reader.Read())
                    {
                        result_widgetInfo.WidgetID = reader.GetInt32(0);
                        result_widgetInfo.OwnerSpaceID = reader.GetInt32(1);
                        result_widgetInfo.WidgetLibInfoID = reader.GetInt32(2);
                        result_widgetInfo.PosX = reader.GetInt32(3);
                        result_widgetInfo.PosY = reader.GetInt32(4);
                        result_widgetInfo.PosZ = reader.GetInt32(5);
                        result_widgetInfo.Height = reader.GetInt32(6);
                        result_widgetInfo.Width = reader.GetInt32(7);
                    }
                }
            }
            catch (Exception error)
            {
                Console.WriteLine(error.Message);
                throw new DataErrorException("WidgetInfoData.Query:执行SqlCommand的时候发生错误！", error);
            }
            finally
            {
                SqlDataManager.CloseConnection(curCommand.Connection);
            }
            if( !HasResult )
            {
                throw new DataNotFoundException("WidgetInfoData.Query:顺利执行完sql语句，可是没有找到匹配的记录！", null);
            }
            return result_widgetInfo;
        }
        ///<summary>
        ///显示所有WidgetInfo信息
        ///</summary>
        ///<exception cref="DataErrorException">数据库执行错误</exception>
        ///<exception cref="DataNotFoundException">查找不到相应记录</exception>
        static public void Show()
        {
            Console.WriteLine("-----WidgetInfo Table-----------------");
            SqlCommand curCommand;
            curCommand = new SqlCommand();
            curCommand.CommandType = CommandType.Text;
            curCommand.CommandText = "select * from WidgetInfo";
            curCommand.Connection = SqlDataManager.OpenConnection();
            try
            {
                SqlDataReader reader = curCommand.ExecuteReader(CommandBehavior.Default);
                while (reader.Read())
                {
                    Console.WriteLine("{0} {1} {2} {3} {4} {5} {6} {7}", reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetInt32(3), reader.GetInt32(4), reader.GetInt32(5), reader.GetInt32(6), reader.GetInt32(7));
                }
            }
            catch (Exception error)
            {
                Console.WriteLine(error.Message);
                throw new DataErrorException("WidgetInfoData.Show:执行SqlCommand的时候发生错误！", error);
            }
            finally
            {
                SqlDataManager.CloseConnection(curCommand.Connection);
                Console.WriteLine("-------------------------------------");
            }
        }
    }
}